<?php
/**
 *  學期班級升級
 * @author cik
 *
 */
class Default_Model_Upgrade_SchoolClass extends Default_Model_Upgrade_Core
{
    protected $_table = 'school_class';
    protected $_targetTable = array('semester_class');

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            //            $query = "ALTER TABLE `semester_class`  ENGINE = MYISAM";
            //            $this->_newdbPdo->exec($query);
            //
            //            $query ="ALTER TABLE `semester_teacher` DROP FOREIGN KEY `semester_teacher_ibfk_1` ;";
            //            $this->_newdbPdo->exec($query);
            //            $query ="ALTER TABLE `semester_teacher` DROP FOREIGN KEY `semester_teacher_ibfk_2` ;";
            //            $this->_newdbPdo->exec($query);
            //            $query = "ALTER TABLE `semester_teacher`  ENGINE = MYISAM";
            //            $this->_newdbPdo->exec($query);

            $query = "SELECT * FROM school_class ";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr= array();
                $arr['year'] = $row['year'];
                $arr['semester'] = $row['semester'];
                $arr['grade'] = $row['c_year'];
                $arr['class_sn'] = $row['c_sort'];
                //                $find = $this->_newdbPdo->query("SELECT class_sn FROM semester_class
                //                     WHERE year='{$arr['year']}'
                //                     AND semester ='{$arr['semester']}'
                //                     AND grade='{$arr['grade']}'
                //                     AND class_sn='{$arr['class_sn']}' ");
                //                if ($find->rowCount() > 0)
                //                continue;
                $arr['chinese_name'] = $this->iconv($row['c_name']);
                $arr['enable'] = ($row['enable']=='1')?'Y':'N';
                $arr['tutor_sn'] = $row['tea_sn_1'];
                $arr['tutor_name_1'] = $this->iconv($row['teacher_1']);
                $arr['tutor_name_2'] = $this->iconv($row['teacher_2']);
                $query  = "INSERT INTO  semester_class (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                $this->_newdbPdo->exec($query);
                // TODO
                // 加入 班級代號class_num

                // 找出導師的帳號
                if ($arr['tutor_name_1']) {
                    $query = "SELECT  username FROM users WHERE name='".trim($arr['tutor_name_1'])."'";
                    $res = $this->_newdbPdo->query($query)->fetch();
                    $teacherId = $res['username'];
                    if ($teacherId) {
                        // 先找 semester_teacher 是否已設有資料
                        $classNum = $arr['grade'].'-'.$arr['class_sn'];
                        $find = $this->_newdbPdo->query("SELECT teacher_id FROM semester_teacher
                      WHERE year='{$arr['year']}'
                     AND semester ='{$arr['semester']}'
                     AND class_num ='$classNum'");
                        if ($find->rowCount() > 0) {
                            $query = "UPDATE  semester_teacher SET class_num='$classNum'
                          WHERE year='{$arr['year']}'
                        AND semester ='{$arr['semester']}'
                        AND teacher_id ='$teacherId'";
                            $this->_newdbPdo->exec($query);
                        }
                        else {
                            // 加入教師設定
                            $arr = array();
                            $arr['year'] = $row['year'];
                            $arr['semester'] = $row['semester'];
                            $arr['teacher_id'] = $teacherId;
                            $arr['class_num'] = $classNum;
                            $this->fromArray('semester_teacher', $arr);
                        }
                    }
                }
            }

        }
        //        $query = "ALTER TABLE `semester_class`  ENGINE = INNODB";
        //        $this->_newdbPdo->exec($query);
        //        $query = "ALTER TABLE `semester_teacher`  ENGINE = INNODB";
        //        $this->_newdbPdo->exec($query);
        //
        //        $query ="ALTER TABLE `semester_teacher` ADD FOREIGN KEY ( `teacher_id` ) REFERENCES `users` (
        //`username`) ON DELETE CASCADE ON UPDATE CASCADE ;";
        //        $this->_newdbPdo->exec($query);
        //
        //        $query ="ALTER TABLE `semester_teacher` ADD FOREIGN KEY ( `title_sn` ) REFERENCES `teacher_title` (
        //`sn`) ON DELETE CASCADE ON UPDATE CASCADE ;";
        //        $this->_newdbPdo->exec($query);

        //  $log['success_count'] = $success_count;
        // $log['error_count'] = $error_count;
        $log['memo'] = '學期班級設定';
        $log['upgrade_time']	= date("Y-m-d h:i:s");

        $this->finishUpgrade($log);

    }

}